﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using myomscl.Utils;

namespace myomscl.DataObjects
{
    public class NewAlertDAL : SqlBaseDal
    {
        internal DataTable GetPersonList(WorkflowFilterSql filterSql)
        {
            var dal = new PersonDAL();
            return dal.GetNetworkOperatorPersonList(filterSql, false);
        }

        internal EstateDataSet.ContractorDataTable GetContractorList(WorkflowFilterSql filterSql)
        {
            var result = new EstateDataSet.ContractorDataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Contractor_GetList;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.MunicipalityId;

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        internal DataTable GetLocationsForMunicipality(WorkflowFilterSql filterSql)
        {
            var result = new EstateDataSet.ContractorDataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Municipality_GetLocations;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.MunicipalityId;

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        internal DataTable GetMunicipalityByNetworkOperator(WorkflowFilterSql filterSql)
        {
            var result = new DataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Municipality_GetByNetworkOperator;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.NetworkOperatorId;

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        internal void SaveAlert(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Alert_Create;

                    var parameters = new SqlParameter[16];
                    parameters[0] = filterSql.AlertCode;
                    parameters[1] = filterSql.AlertType;
                    parameters[2] = filterSql.ActivityType;
                    parameters[3] = filterSql.AlertArea;
                    parameters[4] = filterSql.EstimateStartDate;
                    parameters[5] = filterSql.EstimateEndDate;
                    parameters[6] = filterSql.TrafficComments;
                    parameters[7] = filterSql.TrafficProblems;
                    parameters[8] = filterSql.Location;
                    parameters[9] = filterSql.MunicipalityId;
                    parameters[10] = filterSql.NetworkOperatorId;
                    parameters[11] = filterSql.CenterCoordinates;
                    parameters[12] = filterSql.AreaCoordinates;
                    parameters[13] = filterSql.CurrentStatus;
                    parameters[14] = filterSql.ContactUser;
                    parameters[15] = filterSql.CreatedUser;
                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        internal void DeleteAlert(Int32 alertId, Int32 userId)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Alert_UpdateStatus;

                    WorkflowFilter filter = new WorkflowFilter
                    {
                        AlertId = alertId,
                        //OldStatus = Int32.Created,
                        //NewStatus = Int32.Deleted,
                        UserId = userId
                    };
                    WorkflowFilterSql filterSql = new WorkflowFilterSql(filter);
                    var parameters = new SqlParameter[5];

                    parameters[0] = filterSql.AlertId;
                    parameters[1] = filterSql.NewStatus;
                    parameters[2] = filterSql.OldStatus;
                    parameters[3] = filterSql.Comments;
                    parameters[4] = filterSql.UserId;

                    cmd.Parameters.AddRange(parameters);
                    dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        internal void UpdateAlert(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Alert_Update;

                    var parameters = new SqlParameter[15];
                    parameters[0] = filterSql.AlertId;
                    parameters[1] = filterSql.AlertType;
                    parameters[2] = filterSql.ActivityType;
                    parameters[3] = filterSql.AlertArea;
                    parameters[4] = filterSql.EstimateStartDate;
                    parameters[5] = filterSql.EstimateEndDate;
                    parameters[6] = filterSql.TrafficComments;
                    parameters[7] = filterSql.TrafficProblems;
                    parameters[8] = filterSql.Location;
                    parameters[9] = filterSql.MunicipalityId;
                    parameters[10] = filterSql.CenterCoordinates;
                    parameters[11] = filterSql.AreaCoordinates;
                    parameters[12] = filterSql.CurrentStatus;
                    parameters[13] = filterSql.ContactUser;
                    parameters[14] = filterSql.CreatedUser;
                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }
    }
}
